/*
* 自动生成编码规则
*/
const express = require('express');
const router = express.Router();
// 引入数据库
const db = require("../../../config/db");
// 引入时间包
const moment = require("moment");
// 引入方法库
const { toCamelCase, toUnderlineCase, verifyToken, addZero} = require("../../../config/utils");
// 引入日志
const log = require("../../log");

// 查询获取编码规则列表
router.get("/get/:rule_code",(req, res)=>{
    console.log("===========查询获取编码规则列表接口============");
    // 获取参数
    let {rule_code} = req.params;
    // 生成sql，内连接查询
    let sql = `select rule.rule_code, rule.max_length, rule.rule_code, part.*
    from sys_auto_code_rule rule, sys_auto_code_part part 
    where rule.rule_code = "${rule_code}" and rule.rule_id = part.rule_id`;
    // 执行sql查询语句
    db.query("查询规则表，内连接规则划分表", sql, (err, result) => {
        if (err) {
            res.send({code:500,msg:"查查询规则表，内连接规则划分表失败",data:[]})
            return
        }
        if(result.length > 0){
             // 获取编辑人
            let {username} = verifyToken(req.headers.authorization)
            // 获取更新时间
            let create_time = moment().format('YYYY-MM-DD HH:mm:ss');
            // 生成规则
            let str = "";
            // 规则长度-补零
            let str_length = '';
            // rule_id
            let rule_id = result[0].rule_id;
            // 生成创建时间
            let gen_date =  moment().format('YYYYMMDDHHmmss');
            // 截取年月日
            let date = gen_date.slice(0,8);
             // 查询编码规则结果表当前最大编号
             let sql_max_code = `SELECT last_result as max
             FROM sys_auto_code_result
             WHERE gen_date = (SELECT MAX(gen_date) FROM sys_auto_code_result WHERE rule_id = "${rule_id}")`
            // 物料规则 (年月日 + 当天 + 递增)
            if(rule_code == "ITEM_CODE"){
                // 遍历数组
                result.map(item =>{
                    if(item.part_index = 1 && item.fix_character){
                        str += item.fix_character;
                    }
                })
                str += date;
                // 查询编码规则结果表当前最大编号
                sql_max_code += ` and gen_date like "${date}%"`;
            }
            // (客户规则--供应商规则--车间规则--生产工序规则--设备编码规则--工装夹具类型规则--工装夹具规则) 递增
            let arr_rule_code = ["CLIENT_CODE","VENDOR_CODE","WORKSHOP_CODE","PROCESS_CODE","MACHINERY_CODE","TOOL_TYPE_CODE","TOOL_CODE"];
            if(arr_rule_code.includes(rule_code)){
                // 遍历数组
                result.map(item =>{
                    // 设置前缀
                    if(item.part_index = 1 && item.fix_character){
                        str += item.fix_character;
                    }
                    // 设置补零长度
                    str_length = item.max_length;
                })
            }
            // 删除前缀长度
            str_length = str_length - str.length;
           
            // 执行sql查询语句
            db.query("查询编码规则结果表当前最大编号", sql_max_code, (err, result_max_code) => {
                if (err) {
                    res.send({code:500,msg:"查询编码规则结果表当前最大编号失败",data:[]})
                    return
                }
                if(result_max_code.length > 0){
                    // 物料规则
                    if(rule_code == "ITEM_CODE"){
                        if(result_max_code[0].max != null){
                            // 截取后三位
                            let max_code = result_max_code[0].max.slice(-3);
                            // 生成新的编号
                            let new_code = parseInt(max_code) + 1;
                            // 补零
                            if(new_code < 10){
                                new_code = "00" + new_code;
                            }
                            if(new_code < 100 && new_code >= 10){
                                new_code = "0" + new_code;
                            }
                            str += new_code;
                        }else{
                            str += "001";
                        }
                    }
                    // (客户规则--供应商规则--车间规则--生产工序规则--设备编码规则--工装夹具类型规则--工装夹具规则) 递增
                    if(arr_rule_code.includes(rule_code)){// 截取前缀长度
                        let max = result_max_code[0].max
                        let max_code = max.substring(str.length,max.length+1)
                        // 生成新的编号
                        let new_code = parseInt(max_code) + 1;
                        // 补零
                        new_code = addZero(new_code, str_length);
                        str += new_code;
                    }
                }
                // 插入编码规则结果表
                let sql_insert = `insert into sys_auto_code_result(rule_id,gen_date,last_result,create_time,create_by)
                values("${rule_id}","${gen_date}","${str}","${create_time}","${username}")`;
                // 执行sql查询语句
                db.query("插入编码规则结果表", sql_insert, (err, result_insert) => {
                    if (err) {
                        res.send({code:500,msg:"插入编码规则结果表失败",data:[]})
                        return
                    }
                })
                res.send(str)
            })
            
        }
    })
})

// 获取所有编码规则列表
router.get("/rule/list", async (req, res) => {
    console.log("===========获取编码规则列表接口============");
    // 获取参数
    let { pageNum, pageSize, ruleCode, ruleName, enableFlag } = req.query;
    // 拼接sql语句
    let sql = "select * from sys_auto_code_rule";
    let sql_where = ` where 1=1`;
    if(ruleCode){
        sql_where += ` and rule_code like '%${ruleCode}%'`;
    }
    if(ruleName){
        sql_where += ` and rule_name like '%${ruleName}%'`;
    }
    if(enableFlag){
        sql_where += ` and enable_flag = '${enableFlag}'`;
    }
    if(ruleCode || ruleName || enableFlag){
        sql += sql_where;
    }
    
    // 分页
    sql += ` limit ${(pageNum-1)*pageSize}, ${pageSize}`;
    // 查询数据库
    db.query("查询编码规则列表", sql, (err, result)=>{
        if(err){
            res.send({code: 500, msg: "查询编码规则列表失败", data: err});
            return;
        }
        // 查询总数
        db.query("查询编码规则总数", `select count(*) as count from sys_auto_code_rule ${sql_where}`, (err, count)=>{
            if(err){
                res.send({code: 500, msg: "查询编码规则总数失败", data: err});
                return;
            }
            if(result.length > 0){
                 // 变量Json数组key转成下划线第一个大写
                 result = result.map(item => {
                    return toCamelCase(item)
                })
                res.send({code: 200, msg: "查询编码规则列表成功", rows: result, total: count[0].count});
            }else{
                res.send({code: 200, msg: "查询编码规则列表成功", rows: [], total: 0});
            }
        })
    })
})

// 新增编码规则
router.post("/rule", (req, res)=>{
    console.log("===========新增编码规则接口============");
    // 获取参数
    let {ruleName, ruleCode, ruleDesc, isPadded, enableFlag, maxLength, remark} = req.body;
    // 获取创建人
    let {username} = verifyToken(req.headers.authorization);
    // 获取创建时间
    let createTime = moment().format("YYYY-MM-DD HH:mm:ss");
    // 判断规则名称和规则编码型是否重复
    db.query("查询规则名称和规则编码型是否重复", `select * from sys_auto_code_rule where rule_name = '${ruleName}' or rule_code = '${ruleCode}'`, (err, sql_check_result)=>{
        if(err){
            res.send({code: 500, msg: "查询规则名称和规则编码型是否重复失败", data: err});
            return;
        }
        if(sql_check_result.length > 0){
            res.send({code: 500, msg: "规则名称或规则编码型已存在"});
            return;
        }
        // 插入数据库
        let sql = `insert into sys_auto_code_rule (rule_name, rule_code, rule_desc, is_padded, enable_flag, max_length, remark,  create_by, create_time)
        values 
        ('${ruleName}', '${ruleCode}', ${ruleDesc ? "'"+ruleDesc+"'" : null}, '${isPadded}', '${enableFlag}', ${maxLength ? "'"+maxLength+"'" : null},
        ${remark ? "'"+remark+"'" : null}, '${username}', '${createTime}')`;
        db.query("新增编码规则", sql, (err, result)=>{
            if(err){
                res.send({code: 500, msg: "新增编码规则失败", data: err});
                return;
            }
            if(result.affectedRows > 0){
                res.send({code: 200, msg: "新增编码规则成功"});
            }else{
                res.send({code: 500, msg: "新增编码规则失败"});
            }
        })
    })
})

// 查询单个编码规则
router.get(/^\/rule\/(\d+)$/, (req, res) => {
    console.log("===========查询单个编码规则接口，正则匹配============");
    // 获取参数
    let url_arr = req.url.split("/");
    const id = url_arr[url_arr.length - 1];
   
    db.query("查询单个编码规则", `select * from sys_auto_code_rule where rule_id = ${id}`, (err, result)=>{
        if(err){
            res.send({code: 500, msg: "查询单个编码规则失败", data: err});
            return;
        }
        if(result.length > 0){
            // 变量Json数组key转成下划线第一个大写
            result = result.map(item => {
                return toCamelCase(item)
            })
            res.send({code: 200, msg: "查询单个编码规则成功", data: result[0]});
        }else{
            res.send({code: 500, msg: "查询单个编码规则失败"});
        }
    })
})

// 修改编码规则
router.put("/rule", (req, res)=>{
    console.log("===========修改编码规则接口============");
    // 获取参数
    let {ruleName, ruleCode, ruleDesc, isPadded, enableFlag, maxLength, remark, ruleId} = req.body;
    // 获取修改人
    let {username} = verifyToken(req.headers.authorization);
    // 获取修改时间
    let updateTime = moment().format("YYYY-MM-DD HH:mm:ss");
    // 判断规则名称和规则编码型是否重复
    db.query("查询规则名称和规则编码型是否重复", `select * from sys_auto_code_rule where rule_id != ${ruleId} and ( rule_name = '${ruleName}' or rule_code = '${ruleCode}' )`, (err, result)=>{
        if(err){
            res.send({code: 500, msg: "查询规则名称和规则编码型是否重复失败", data: err});
            return;
        }
        if(result.length > 0){
            res.send({code: 500, msg: "规则名称或规则编码型已存在", data: []});
            return;
        }
        // 修改编码规则sql
        let sql = `update sys_auto_code_rule set rule_name = '${ruleName}',  rule_code = '${ruleCode}', is_padded = '${isPadded}',
        enable_flag = '${enableFlag}', rule_desc = ${ruleDesc ? "'"+ruleDesc+"'" : null}, max_length = ${maxLength ? "'"+maxLength+"'" : null},
        remark=${remark ? "'"+remark+"'" : null}, update_by = '${username}', update_time = '${updateTime}'
        where rule_id = ${ruleId}`;
        db.query("修改编码规则", sql, (err, result)=>{
            if(err){
                res.send({code: 500, msg: "修改编码规则失败", data: err});
                return;
            }
            if(result.affectedRows > 0){
                res.send({code: 200, msg: "修改编码规则成功", data: []});
            }else{
                res.send({code: 500, msg: "修改编码规则失败", data: []});
            }
        })
    })
})

// 删除编码规则
router.delete("/rule/:id", (req, res) => {
    console.log("===========删除编码规则接口============");
    // 获取参数
    let {id} = req.params;
     // 拼接sql,删除多条数据
     let sql = `delete from sys_auto_code_rule where rule_id in(${id})`;
    // 查询数据库
    db.query("删除编码规则", sql, (err, result) => {
        if (err) {
            res.send({code: 500, msg: "删除编码规则失败", data: err});
            return
        }
        if(result.affectedRows > 0){
            res.send({code: 200, msg: "删除编码规则成功"})
        }else{
            res.send({code: 500, msg: "删除编码规则失败"})
        }
    })
})


module.exports = router;